<?php
include ("global_files/filter_global_pgsql.inc");
include ("global_files/global_functions.inc");
include ("global_files/global_session.inc");
include ("global_files/tables.inc");
$sql= new pgSql();
$db= new cDatabase();
$company_id="_".$_SESSION["gmember_id"];
function createNewReport($report_code, $report_name,$table_name)
        {
        global $db,$sql,$company_id, $sys_report_customization_table;
	//entry in master_report
	$db->query="SELECT report_id FROM $company_id.master_report WHERE report_code='$report_code' ;";
	$db->runQuery();
	$report_id_array=$db->returnArrays();

	if($report_id_array[0]["report_id"])
		{
		$db->query="update $company_id.master_report set table_view_name='".trim($table_name)."' where report_code='".$report_code."'";
		$db->runQuery();
		}
	else
		{
		$db->query="insert into $company_id.master_report (report_code, report_name,table_view_name) values ('".trim($report_code)."','".trim($report_name)."','".trim($table_name)."');";
		$db->runQuery();
		}
       //fetch the report id and insert a default customziation for this report
       $db->query="insert into $sys_report_customization_table (report_id, customization_name, is_active) select report_id::integer, 'Default','t' from $company_id.master_report where report_code='".trim($report_code)."';";
	$db->runQuery();
       }

function insertNewColumns($report_code, $columns_array1, $columns_array2)
        {
        global $db,$sql,$company_id;
	$db->query = "select report_id from "."$company_id"."."."master_report"." where report_code='$report_code'";
	$db->runQuery();
	$report_array = $db->returnArrays();
	$report_id=$report_array[0]["report_id"];

	$db->query = "select customization_id from "."$company_id"."."."sys_report_customization"." where report_id='$report_id'";
	$db->runQuery();
	$custom_array = $db->returnArrays();
	$customization_id=$custom_array[0]["customization_id"];

        $c=count($columns_array1);
        for($i=0; $i<$c; $i++)
                {
                foreach($columns_array1[$i] as $key=>$value)
                        {
                        $columns_array1[$i][$key]=trim($value);
                        }
			//entry in master column mapping
		$columns_array1[$i]["report_id"]=$report_id;
		$sql->table = "$company_id".".master_column_mapping";
		$sql->column = $columns_array1[$i];

		$sql->generateInsertQuery();
		$db->query = $sql->query;
		$db->runQuery();

		if ($columns_array2[$i]["set_default"]=="t")
			{
			$default_customization="t";
			}
		else
			$default_customization="f";
	
		unset($columns_array2[$i]["set_default"]);

		foreach($columns_array2[$i] as $key=>$value)
                        {
                        $columns_array2[$i][$key]=trim($value);
                        }
		$db->query="select column_id from $company_id.master_column_mapping where report_id=$report_id and script_variable_name='".$columns_array1[$i]["script_variable_name"]."' ;";
		$db->runQuery();
		$column_id_array=$db->returnArrays();
		$column_id=$column_id_array[0]["column_id"];
                $columns_array2[$i]["column_id"]=$column_id;
		//entry in master_report_customization
		$sql->table = "$company_id".".master_report_customization";
		$sql->column = $columns_array2[$i];
		$sql->generateInsertQuery();
		$db->query = $sql->query;
		$db->runQuery();
		if ($default_customization=="t")
			{
			$columns_array2[$i]["customization_id"]=$customization_id;
			$sql->table = $company_id.".report_customization";
			$sql->column = $columns_array2[$i];
			$sql->generateInsertQuery();
			$db->query = $sql->query;
			$db->runQuery();
			}
		}
	echo "Successfully created report.<br/> Report Id : $report_id <br/> Report Code : $report_code";
        }

function updateColumns($report_code, $columns_array1, $columns_array2)
        {
        global $db,$sql;
        $c=count($columns_array1);
        for($i=0; $i<$c; $i++)
                {
                $condition_array=array ("report_code"=>$report_code, "script_variable_name"=>$columns_array1[$i]["script_variable_name"]);
		$sql->table= "$company_id".".master_column_mapping";
        	$sql->column[]="column_id";
		$sql->condition[]="report_code='$report_code'";
		$sql->generateSelectQuery();
		$db->runQuery();
                $column_id_array=$db->returnArrays();
                $condition_array1["column_id"]=$column_id_array[0]["column_id"];

                foreach($columns_array1[$i] as $key=>$value)
                        {
                        $columns_array1[$i][$key]=trim($value);
                        }
		$sql->column = $columns_array1[$i];
		$sql->table = "$company_id".".master_column_mapping";
		$sql->condition = $condition_array1;
		$sql->generateUpdateQuery();
		$db->query = $sql->query;
		$db->runQuery();

                foreach($columns_array2[$i] as $key=>$value)
                        {
                        $columns_array2[$i][$key]=trim($value);
                        }
		$sql->column = $columns_array2[$i];
		$sql->table = "$company_id".".master_report_customization";
		$sql->condition = $condition_array1;
		$sql->generateUpdateQuery();
		$db->query = $sql->query;
		$db->runQuery();if ($columns_array2[$i]["set_default"]=="y")
			{
			unset($columns_array2[$i]["set_default"]);
			$columns_array2[$i]["customization_id"]=$customization_id;
			$sql->table = "$company_id".".report_customization";
			$sql->column = $columns_array2[$i];
			$sql->generateInsertQuery();
			$db->query = $sql->query;
			$db->runQuery();
			}
		}
	}

function createNewFilters($columns_array1)
	{
	global $db,$sql,$company_id;
	$c=count($columns_array1);
	for($i=0; $i<$c; $i++)
		{
		$sql->column=$columns_array1[$i];
		$sql->exclude_columns=array();
		$sql->table=$company_id.".sys_master_filter";
		$sql->generateInsertQuery();
		$db->query=$sql->query;
		$db->runQuery();
		}
	}

function insertNewReportFilters($columns_array1)
        {
        global $db, $sys_master_filter_table,$master_report_table,$master_column_mapping_table,$sys_report_filter_table,$sys_master_filter_table, $sys_filter_customization_table,$company_id;
         $c=count($columns_array1);

       for($i=0; $i<$c; $i++)
                {
                foreach($columns_array1[$i] as $key=>$value)
                        {
                        $columns_array1[$i][$key]=trim($value);
                        }
		}
	for($i=0; $i<$c; $i++)
                {
		$db->query="select report_id,table_view_name from ".$master_report_table." where report_code='".$columns_array1[$i][report_code]."';";
		$db->runQuery();
		$sys_report_filters=$db->returnArrays();
		$report_id=$sys_report_filters_values["report_id"]=$sys_report_filters[0]["report_id"];
		$table_view_name=$sys_report_filters_values["table_view_name"]=$sys_report_filters[0]["table_view_name"];
		
		$db->query="select filter_id from ".$sys_master_filter_table." where filter_name='".$columns_array1[$i][filter_name]."' and filter_table_join='".$table_view_name."';";
		$db->runQuery();
		$sys_report_filters=$db->returnArrays();
		$filter_id=$sys_report_filters_values["filter_id"]=$sys_report_filters[0]["filter_id"];

		
		$db->query = "select customization_id from "."$company_id"."."."sys_report_customization"." where report_id='$report_id'";
		$db->runQuery();
		$custom_array = $db->returnArrays();
		$customization_id=$custom_array[0]["customization_id"];


		$db->query="select column_id from ".$master_column_mapping_table." where script_variable_name='".$columns_array1[$i][script_variable_name]."' and report_id=".$sys_report_filters_values[report_id].";";
		$db->runQuery();
		$master_column_id=$db->returnArrays();
		//$group_column=$columns_array1[$i]["group_column"];
		$column_id=$group_column=$sys_report_filters_values["filter_column"]=$master_column_id[0]["column_id"];
			$db->query="insert into $sys_report_filter_table(report_id,filter_id,filter_column, group_column) values($report_id,$filter_id,$column_id, $group_column);";
		$db->runQuery();

		if ($columns_array1[$i]["set_default"]=="t")
			{
			$db->query="insert into $sys_filter_customization_table(customization_id,filter_id,filter_display_name, filter_order,set_default) select $customization_id, $filter_id, form_display, $i+1, set_default from $sys_master_filter_table where filter_id=$filter_id;";
			$db->runQuery();
			}
		}
	}
?>
